﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace RantopadService.Business
{
    public class OptionBusiness
    {
        public static DataTable getAllOptions(string wherelike)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where (a.USER_ID like '%{0}%' )  and a.DEV_ID=b.ID ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getAllOptions(string wherelike, string deviceId)
        {
            if (deviceId == "0")
                return getAllOptions(wherelike);
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where (a.USER_ID like '%{0}%' ) and a.DEV_ID = '{1}' and a.DEV_ID=b.ID ";
            //string mysql = "SELECT * from `option`  where (USER_ID like '%{0}%' ) and DEV_ID like '%{1}%'";
            mysql = string.Format(mysql, wherelike, deviceId);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static int getAllOptionsCount()
        {
            string mysql = "SELECT count(*) as count from `option` ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }
        public static int getAllOptionsCount(string wherelike, string deviceId)
        {
            string mysql = "SELECT count(*) as count from `option` where (USER_ID like '%{0}%' )  and DEV_ID like '%{1}%'";
            mysql = string.Format(mysql, wherelike, deviceId);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }


        public static int delOptionByID(string ID)
        {
            string mysql = "delete from `option`  where ID='{0}';      delete from `key_setting_of_option` where `OPTION_ID`='{0}';    delete  from  key_light  where `OPTION_ID`='{0}'";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }

        public static int delKeySetting(string optionid)
        {
            string mysql = "  delete from `key_setting_of_option` where `OPTION_ID`='{0}'";
            mysql = string.Format(mysql, optionid);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }
        public static int delKeyLight(string optionid)
        {
            string mysql = "  delete  from  key_light  where `OPTION_ID`='{0}'";
            mysql = string.Format(mysql, optionid);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }

        public static DataRow getOptionByID(string ID)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where a.ID='{0}' ";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt.Rows[0];
        }
        public static DataTable getOptionTByID(string ID)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where a.ID='{0}' and a.dev_id=b.id";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getOptionByUserID(string UserID)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where a.USER_ID='{0}' and a.dev_id=b.id";
            mysql = string.Format(mysql, UserID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static bool isOptionExist(string devId, string name, string userId)
        {
            string mysql = "SELECT  * from `option` a where a.USER_ID='{0}' and a.dev_id='{1}' and a.option_name='{2}'";
            mysql = string.Format(mysql, userId, devId, name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                myconnection.Close();
                return true;
            }
            myconnection.Close();
            return false;
        }
        public static DataTable getOptionTByID(string ID, string optionVersion)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from `option` a,device b where a.ID='{0}' and VERSION>'{1}' ";
            mysql = string.Format(mysql, ID, optionVersion);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getTopOptions(int top, string name)
        {
            string mysql = "SELECT id, user_id,option_name,up,down FROM rantopad.`option`  where   option_name like '%{1}%' order by up desc LIMIT {0} ";
            mysql = string.Format(mysql, top.ToString(), name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getTopOptions(int location, int size, string name, int orderpara)
        {
            var orderstr = "";
            switch (orderpara)
            {
                case 0: orderstr = " order by up desc,down "; break;
                case 1: orderstr = " order by time desc"; break;
                default:
                    break;
            }
            string mysql = "SELECT id, user_id,option_name,up,down FROM rantopad.`option`  where   option_name like '%{0}%' {3} LIMIT {1},{2} ";
            mysql = string.Format(mysql, name, location.ToString(), size.ToString(), orderstr);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getTopMacros(int top, string name)
        {
            string mysql = "SELECT  * FROM rantopad.`sharemacro`  where    macro_name like '%{1}%' order by up desc LIMIT {0} ";
            mysql = string.Format(mysql, top.ToString(), name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getShareMacro(string userName, string name)
        {
            string mysql = "SELECT  *  FROM rantopad.`sharemacro`  where    macro_name like '%{1}%' and user_name ='{0}' ";
            mysql = string.Format(mysql, userName, name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static string addNewOption(string devId, string name, string userId, string refrese, string sensibility, DataTable keySetting, DataTable keyLight, DateTime time, string optionhash)
        {
            if (isOptionExist(devId, name, userId))
            {
                return "exist";
            }
            int count = 0;
            try
            {
                //判断是否已存在，存在则更新
                string optionid = GetOptionIdByPid(devId, name, userId);
                if (optionid != "")
                {
                    var r = updateOption(optionid, devId, name, userId, refrese, sensibility, time, optionhash);
                    if (r > 0)
                    {
                        OptionBusiness.delKeyLight(optionid);
                        OptionBusiness.delKeySetting(optionid);
                        OptionBusiness.addNewKeyLightOfOption(keyLight);
                        OptionBusiness.addNewKeySettingOfOption(keySetting);
                        return optionid;
                    }
                    return "";
                }
                //不存在则插入
                string mysql = "insert into `option` (`DEV_ID`, `OPTION_NAME`, `USER_ID`, `REFRESE`, `SENSIBILITY`, `OPTION_TIME`, `OPTION_HASH`) VALUES ('{0}', '{1}', '{2}', '{3}','{4}', '{5}','{6}') ";
                mysql = string.Format(mysql, devId, name, userId, refrese, sensibility, time, optionhash);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    string lastOptionID = getLastedOptionID();
                    foreach (DataRow item in keySetting.Rows)
                    {
                        item["OPTION_ID"] = lastOptionID;
                    }
                    foreach (DataRow item in keyLight.Rows)
                    {
                        item["OPTION_ID"] = lastOptionID;
                    }
                    addNewKeySettingOfOption(keySetting);
                    addNewKeyLightOfOption(keyLight);
                    myconnection.Close();
                    return lastOptionID;
                }
            }
            catch (Exception)
            {

                count = 0;

            }



            return "";
        }
        public static int updateOption(string id, string devId, string name, string userId, string refrese, string sensibility, DateTime time, string optionhash)
        {
            int count = 0; var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update  `option` set `DEV_ID`='{1}', `OPTION_NAME`='{2}', `USER_ID`='{3}', `REFRESE`='{4}', `SENSIBILITY`='{5}', `OPTION_TIME`='{6}', `OPTION_HASH`='{7}', version=version+1 where `ID`='{0}' ";
                mysql = string.Format(mysql, id, devId, name, userId, refrese, sensibility, time, optionhash);
                //创建数据库连接

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }



            return count;
        }
        public static int updateOption(string id, string devId, string name, string userId, string refrese, string sensibility, string optionVersion, DateTime time, string optionhash)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update  `option` set `DEV_ID`='{1}', `OPTION_NAME`='{2}', `USER_ID`='{3}', `REFRESE`='{4}', `SENSIBILITY`='{5}', `OPTION_TIME`='{6}', `OPTION_HASH`='{7}' version=version+1 where `ID`='{0}' and '{6}'>=version ";
                mysql = string.Format(mysql, id, devId, name, userId, refrese, sensibility, optionVersion, time, optionhash);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }
            return count;
        }
        public static int upOption(string id, string operation)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `option` set up=up" + operation + "1  where `ID`='{0}' ";
                mysql = string.Format(mysql, id);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }



            return count;
        }

        public static int shareOption(string id)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `option` set share=1  where `ID`='{0}' ";
                mysql = string.Format(mysql, id);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }
            return count;
        }
        public static int unshareOption(string id)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `option` set share=0  where `ID`='{0}' ";
                mysql = string.Format(mysql, id);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }
            return count;
        }


        public static int shareMacro(string userName, string macroName, string macroContent)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "insert into `shareMacro` (`user_name`, `macro_name`, `content`, `time`) VALUES ('{0}', '{1}', '{2}',  '{3}') ON DUPLICATE KEY UPDATE `content`='{2}' , `time`= '{3}'";

                mysql = string.Format(mysql, userName, macroName, macroContent, DateTime.Now.ToString());

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }
            return count;
        }
        public static int unshareMacro(string strUserId, string strMacroName)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "delete from `sharemacro`   where `user_name`='{0}' and `macro_name`='{1}' ";
                mysql = string.Format(mysql, strUserId, strMacroName);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }
            return count;
        }
        public static int upShareMacro(string strUserId, string strMacroName, string operation)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `sharemacro` set  up=up" + operation + "1   where `user_name`='{0}' and `macro_name`='{1}' ";
                mysql = string.Format(mysql, strUserId, strMacroName);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }
            return count;
        }

        public static int downShareMacro(string strUserId, string strMacroName, string operation)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `sharemacro` set  down=down" + operation + "1  where `user_name`='{0}' and `macro_name`='{1}' ";
                mysql = string.Format(mysql, strUserId, strMacroName);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }
            return count;
        }
        public static int downOption(string id, string operation)
        {
            int count = 0;//创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {
                string mysql = "update `option` set down=down" + operation + "1  where `ID`='{0}' ";
                mysql = string.Format(mysql, id);

                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }



            return count;
        }
        private static string getLastedOptionID()
        {
            string mysql = "SELECT  max(a.ID)as ID from `option` a  ";

            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt.Rows[0][0].ToString();
        }

        public static int addNewKeySettingOfOption(DataTable keySetting)
        {
            int count = 0; var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {


                for (int i = 0; i < keySetting.Rows.Count; i++)
                {
                    string mysql = "insert into `key_setting_of_option` (`OPTION_ID`, `KEY_ID`, `KEY_SETTING_ID`, `DETAIL`, `EXTEND`) VALUES ('{0}', '{1}', '{2}',  '{3}',  '{4}') ";

                    mysql = string.Format(mysql, keySetting.Rows[i]["OPTION_ID"], keySetting.Rows[i]["KEY_ID"], keySetting.Rows[i]["KEY_SETTING_ID"], keySetting.Rows[i]["DETAIL"], keySetting.Rows[i]["EXTEND"]);
                    //创建数据库连接

                    MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                    count = count + mycommand.ExecuteNonQuery();
                }

                myconnection.Close();

                if (count > 0)
                {

                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;

            }



            return count;
        }
        public static int addNewKeyLightOfOption(DataTable keySetting)
        {
            int count = 0; var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            try
            {


                for (int i = 0; i < keySetting.Rows.Count; i++)
                {
                    DataRow row = keySetting.Rows[i];
                    string mysql = "insert into `key_light` (`OPTION_ID`, `KEY_ID`, `R`, `G`, `B`, `BREATHE`, `IF_LIGHT`) VALUES ('{0}', '{1}', '{2}',  '{3}',  '{4}',  '{5}',  '{6}') ";
                    mysql = string.Format(mysql, row["OPTION_ID"], row["KEY_ID"], row["R"], row["G"], row["B"], booltoint(row["BREATHE"].ToString()), booltoint(row["IF_LIGHT"].ToString()));

                    //创建数据库连接
                    MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                    count = count + mycommand.ExecuteNonQuery();
                }
                myconnection.Close();
                if (count > 0)
                {
                    return count;
                }
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }
            return count;
        }
        private static string booltoint(string bo)
        {
            string strint = "0";
            switch (bo)
            {
                case "True": strint = "1";
                    break;
                case "False": strint = "0";
                    break;
                case "true": strint = "1";
                    break;
                case "false": strint = "0";
                    break;
                default:
                    strint = bo;
                    break;

            }
            return strint.ToString();
        }
        public static DataTable getAllKeyLightByID(string ID)
        {
            string mysql = " select a.* FROM key_light a where   a.OPTION_ID={0}";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();
            return dt;
        }

        internal static string GetOptionIdByPid(string strPid, string optionname, string userid)
        {
            string mysql = "SELECT ID FROM rantopad.`option` a where a.DEV_ID='{0}' and a.OPTION_NAME='{1}' and a.USER_ID='{2}' ";
            mysql = string.Format(mysql, strPid, optionname, userid);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            return dt.Rows[0][0].ToString();
        }
        internal static List<string> GetAllOptionName(string strPid, string userid)
        {
            string mysql = "SELECT OPTION_NAME FROM rantopad.`option` a where a.DEV_ID='{0}'  and a.USER_ID='{1}' ";
            mysql = string.Format(mysql, strPid, userid);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();
            if (dt.Rows.Count == 0)
            {
                return null;
            }

            List<string> nameList = new List<string>();
            foreach (DataRow item in dt.Rows)
            {
                nameList.Add(item["OPTION_NAME"].ToString());
            }
            return nameList;
        }

        public static DataTable getTopMacros(int location, int size, string p, int orderpara)
        {
            var orderstr = "";
            switch (orderpara)
            {
                case 0: orderstr = " order by up desc,down "; break;
                case 1: orderstr = "order by time desc"; break;
                default:
                    break;
            }
            string mysql = "SELECT  * FROM rantopad.`sharemacro`  where    macro_name like '%{0}%' {3} LIMIT {1},{2} ";
            mysql = string.Format(mysql, p, location.ToString(), size.ToString(), orderstr);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
    }
}